package jude.java.money.util;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;


public class MysqlConnection {
	
	private static final String DRIVER = "com.mysql.jdbc.Driver";
	
	private String host = "localhost";
	private String database;
	private String username = "root";
	private String password = "jude";
	
	private Connection conn;
	
	public MysqlConnection(String db) {
		this.database = db;
	}
	
	public MysqlConnection(String host, String db, String user, String pwd) {
		this.host = host;
		this.database = db;
		this.username = user;
		this.password = pwd;
	}
	
	public boolean connect() {
		try {
			if (this.conn != null && !this.conn.isClosed())
				return true;
			String url = getUrl();
			Class.forName(DRIVER);
			Connection conn = DriverManager.getConnection(url, username, password);

			if(conn.isClosed()) {
				System.err.println("Can't connect to the database!");
				return false;
			}

			this.conn = conn;
			return true;
		} catch (Exception e) {
			System.err.println(e.getClass().getName());
		}
		return false;
	}
	
	public int executeUpdate(String sql) throws SQLException {
		if (conn == null || conn.isClosed())
			if (!connect())
				return -1;
		Statement statement = conn.createStatement();
		int r = statement.executeUpdate(sql);
		statement.close();
		return r;
	}
	
	public List<Object[]> executeQuery(String sql) throws SQLException {
		if (conn == null || conn.isClosed())
			if (!connect())
				return null;
		Statement statement = conn.createStatement();
//		PreparedStatement statement = conn.prepareStatement(sql,ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY); 
		ResultSet rs = statement.executeQuery(sql);
		int columnCount = rs.getMetaData().getColumnCount();
		List<Object[]> r = new ArrayList<Object[]>();
		while(rs.next()) {
			Object[] row = new Object[columnCount];
			for (int i = 0; i < row.length; i++) {
				row[i] = rs.getObject(i + 1);
			}
			r.add(row);
		}
		rs.close();
		statement.close();
		return r;
	}
	
	@SuppressWarnings("unchecked")
	public <T> T executeUniqueResultQuery(String sql) throws SQLException {
		if (conn == null || conn.isClosed())
			if (!connect())
				return null;
		Statement statement = conn.createStatement();
		ResultSet rs = statement.executeQuery(sql);
		int columnCount = rs.getMetaData().getColumnCount();
		if (columnCount != 1) {
			System.err.println("not unique result");
			rs.close();
			statement.close();
			return null;
		}
		while(rs.next()) {
			if (!rs.isLast()) {
				System.err.println("not unique result");
				rs.close();
				statement.close();
				return null;
			}
			Object o = rs.getObject(1);
			rs.close();
			statement.close();
			return (T)o;
		}
		return null;
	}
	
	public void close() {
		if (conn != null) {
			try {
				conn.close();
			} catch (SQLException e) {
			}
			conn = null;
		}
	}

	private String getUrl() {
		return "jdbc:mysql://" + host + ":3306/" + database;
	}
	
	public static void main(String[] args) throws SQLException {
		MysqlConnection conn = new MysqlConnection("zc");
		System.out.println(conn.executeUniqueResultQuery("select admin_name from admin"));
		List<Object[]> r = conn.executeQuery("select * from admin");
		for (Object[] row : r) {
			for (int i = 0; i < row.length; i++) {
				System.out.print(row[i] + " ");
			}
			System.out.println();
		}
		conn.close();
	}
}
